package gotexcel

import (
	"errors"
	"fmt"
	"os"
	"strconv"
	"strings"

	"gitee.com/tommickey/gose/v3/gose/gotengine"
	"gitee.com/tommickey/gose/v3/gose/gotexcel/excelize"
	"gitee.com/tommickey/gose/v3/gose/gotlib"
)

var appParamsMap map[string]string
var apis map[string]interface{}

func ReadExcel(fileName, sheetName string) (data [][]string, err error) {
	f, err := excelize.OpenFile(fileName)
	if err != nil {
		gotlib.LogE(err)
		return
	}
	defer func() {
		if err = f.Close(); err != nil {
			gotlib.LogE(err)
		}
	}()

	// 获取 sheetName 上所有单元格
	data, err = f.GetRows(sheetName)
	if err != nil {
		gotlib.LogE(err)
		return
	}
	return data, nil
}

// CreateExcelFileBaseTemplate 根据Excel自定义模板导出数据
// 支持多文件、多Sheet导出。
// Excel Template格式参考文件：ExcelExportTemplateSample.xlsx
func CreateExcelFileBaseTemplate(templateFile, sheetName string) (newFileNames []string, err error) {
	data, err := ReadExcel(templateFile, sheetName)
	if err != nil {
		gotlib.LogE("不能打开模板文件：", templateFile, sheetName)
		return
	}

	// 模板说明
	// ABC列：基本配置、类型、内容
	baseMap, err := GetExcelConfigMapStrAStr(data, 3, 1, 3)
	if err != nil {
		return
	}
	fixMap, err := GetExcelConfigMapStrAStr(data, 3, 4, 3)
	if err != nil {
		return
	}
	dataMap, err := GetExcelConfigMapStrAStr(data, 3, 7, 3)
	if err != nil {
		return
	}
	mergeMap, err := GetExcelConfigMapStrAStr(data, 3, 10, 3)
	if err != nil {
		return
	}
	styleMap, err := GetExcelConfigMapStrAStr(data, 3, 13, 3)
	if err != nil {
		return
	}
	appParamsMap = make(map[string]string)
	apis = gotengine.GetDefaultEngineAPIs()

	// 获取目标文件名列表
	var FileNameList []string
	if temp, err := GetExcelConfigValue("FileNameList", baseMap["FileNameList"], appParamsMap, apis); err != nil {
		return newFileNames, err
	} else {
		// 根据文件名列表配置的返回值，获取文件名列表
		FileNameList = InterfaceValueToList(temp, "FileName")
	}
	appParamsMap["FileNameList"] = gotlib.StrJoin(",", FileNameList...)
	for k, v := range FileNameList {
		// 确定目标文件名 FileName
		fmt.Println(k, v)
		FileName := v
		appParamsMap["FileName"] = FileName

		// 初始化新Excel
		f := excelize.NewFile()

		// 获取SheetNameList
		var SheetNameList []string
		if tempSheetName, err := GetExcelConfigValue("SheetNameList", baseMap["SheetNameList"], appParamsMap, apis); err != nil {
			return newFileNames, err
		} else {
			// 根据文件名列表配置的返回值，获取文件名列表
			SheetNameList = InterfaceValueToList(tempSheetName, "SheetName")
		}
		appParamsMap["SheetNameList"] = gotlib.StrJoin(",", SheetNameList...)
		// 根据SheetName进行处理
		for k1, v1 := range SheetNameList {
			fmt.Println(k1, v1)
			SheetName := v1
			appParamsMap["SheetName"] = SheetName

			// 创建SheetName，并设置为活动sheet
			if sheetName != "Sheet1" {
				iSheetIndex := f.NewSheet(SheetName)
				f.SetActiveSheet(iSheetIndex)
			} else {
				f.SetActiveSheet(f.GetSheetIndex("Sheet1"))
			}

			// 向当前活动Sheet里面写数据
			err = SetSheetData(f, SheetName, appParamsMap, baseMap, fixMap, dataMap)
			if err != nil {
				return newFileNames, err
			}
			// 数据合并
			err = SetSheetMerge(f, SheetName, appParamsMap, mergeMap)
			if err != nil {
				return newFileNames, err
			}
			// 数据样式
			err = SetSheetStyle(f, SheetName, appParamsMap, styleMap)
			if err != nil {
				return newFileNames, err
			}
			// 其它

		}
		// 保存文件
		err = f.SaveAs(FileName)

	}

	return FileNameList, err
}

func SetSheetStyle(f *excelize.File, sheetName string, appParamsMap map[string]string, styleMap map[string][]string) (err error) {
	for k, v := range styleMap {
		cells := strings.Split(k, ":")
		if len(cells) == 1 {
			cells = append(cells, cells[0])
		} else if len(cells) == 0 {
			gotlib.LogW("Excel设置样式位置错误：", sheetName, k)
		}

		temp, err := GetExcelConfigValue(k, v, appParamsMap, apis)
		if err != nil {
			return err
		} else {
			// 创建样式
			styleId, _ := f.NewStyle(temp.(string))
			err = f.SetCellStyle(sheetName, cells[0], cells[1], styleId)
			if err != nil {
				return err
			}
		}
	}
	return
}

func SetSheetMerge(f *excelize.File, sheetName string, appParamsMap map[string]string, mergeMap map[string][]string) (err error) {
	for k, v := range mergeMap {
		cells := strings.Split(k, ":")
		if len(cells) != 2 {
			gotlib.LogW("Excel 单元格无法合并:", sheetName, k)
		}
		temp, err := GetExcelConfigValue(k, v, appParamsMap, apis)
		if err != nil {
			return err
		}
		if temp.(string) == "合并" {
			//gotlib.PrintRedLn("合并：", cells)
			err = f.MergeCell(sheetName, cells[0], cells[1])
			if err != nil {
				return err
			}
		}
	}
	return
}

func SetSheetData(f *excelize.File, sheetName string, appParamsMap map[string]string, baseMap, fixMap, dataMap map[string][]string) (err error) {
	for k, v := range baseMap {
		if !(k == "FileNameList" || k == "SheetNameList" || k == "Data") {
			temp, err := GetExcelConfigValue(k, v, appParamsMap, apis)
			if err != nil {
				return err
			}
			appParamsMap[k] = gotlib.String(temp)
		}
	}

	for k, v := range fixMap {
		temp, err := GetExcelConfigValue(k, v, appParamsMap, apis)
		if err != nil {
			return err
		}
		f.SetCellValue(sheetName, k, temp)
	}

	temp, err := GetExcelConfigValue("Data", baseMap["Data"], appParamsMap, apis)
	if err != nil {
		return
	}
	fmt.Println(temp)

	err = SetExcelDataList(f, sheetName, temp, appParamsMap, fixMap, dataMap)
	if err != nil {
		return
	}
	return
}

func SetExcelDataList(f *excelize.File, sheetName string, value interface{}, appParamsMap map[string]string, fixMap, dataMap map[string][]string) (err error) {
	if value == nil {
		return
	}

	dataStartRow := int(gotlib.Int64(appParamsMap["DataStartRow"]))

	if _, ok := value.([]map[string]interface{}); ok {
		for k, v := range value.([]map[string]interface{}) {
			for dataKey, dataValue := range dataMap {
				RC := gotlib.StrJoin("", dataKey, gotlib.String(k+dataStartRow))
				err = f.SetCellValue(sheetName, RC, v[dataValue[1]])
				if err != nil {
					return
				}
			}
		}
		return
	}
	if _, ok := value.([]map[string]string); ok {
		for k, v := range value.([]map[string]string) {
			for dataKey, dataValue := range dataMap {
				RC := gotlib.StrJoin("", dataKey, gotlib.String(k+dataStartRow))
				err = f.SetCellValue(sheetName, RC, v[dataValue[1]])
				if err != nil {
					return
				}
			}
		}
		return
	}
	return

}

// InterfaceValueToList 计算多文件名，多Sheet名数组
func InterfaceValueToList(value interface{}, mapKey string) (list []string) {
	_, ok := value.([]map[string]interface{})
	if ok {
		for _, v := range value.([]map[string]interface{}) {
			list = append(list, v[mapKey].(string))
		}
		return
	}
	_, ok = value.([]map[string]string)
	if ok {
		for _, v := range value.([]map[string]string) {
			list = append(list, v[mapKey])
		}
		return
	}
	list, ok = value.([]string)
	if ok {
		return
	}
	_, ok = value.(string)
	if ok {
		list = []string{value.(string)}
		return
	}
	list = []string{gotlib.String(value)}
	return
}

// GetValueType 该方法非常不准确，目前对简单的string，[]string可以识别，其它的准确性，有待检查。
func GetValueType(value interface{}) (valueType string) {
	switch value.(type) {
	case string:
		return "string"
	case []string:
		return "[]string"
	case []interface{}:
		return "[]interface{}"
	case interface{}:
		_, ok := value.([]map[string]interface{})
		if ok {
			return "[]map[string]interface{}"
		}
		_, ok = value.([]map[string]string)
		if ok {
			return "[]map[string]string"
		}
		return "interface{}"
	case int:
		return "int"
	case []int:
		return "[]int"
	case int64:
		return "int64"
	case []int64:
		return "[]int64"
	case []map[string]interface{}:
		return "[]map[string]interface{}"
	default:
		return "other"
	}
	return
}

func GetExcelConfigValue(key string, config []string, appParamsMap map[string]string, apis map[string]interface{}) (value interface{}, err error) {
	if len(config) != 2 {
		return "", nil
	}
	switch config[0] {
	case "固定值":
		return config[1], nil
	case "逻辑配置":
		ret, err := gotengine.RunEngineConfigOnce(key, key, config[1], appParamsMap, apis)
		if err != nil {
			return "", err
		} else {
			return ret, nil
		}
	default:
		return "", nil
	}
}

// GetExcelConfigMapStrAStr 获取Excel数据表data[][]中指定开始行，开始列，总宽度区域内的数据，转换为map[string][]string类型。
func GetExcelConfigMapStrAStr(data [][]string, startRow, startCol, totalCols int) (configMap map[string][]string, err error) {
	if len(data) < startRow {
		return nil, errors.New("没有发现基本配置信息。")
	}
	if len(data[2]) < startCol+totalCols-1 {
		return nil, errors.New("没有发现基本配置信息。")
	}
	configMap = make(map[string][]string)
	for i := startRow - 1; i < len(data); i++ {
		rowLen := len(data[i])
		if rowLen < startCol {
			break
		}
		k := data[i][startCol-1]
		if k == "" {
			continue
		}
		var v []string
		for j := startCol; j < startCol+totalCols-1; j++ {
			if rowLen > j {
				v = append(v, data[i][j])
			} else {
				v = append(v, "")
			}
		}

		configMap[k] = v

	}
	return
}

// GetExcelConfigMapStrStr 获取Excel数据表data[][]中指定开始行，开始列，2列宽度区域内的数据，转换为map[string]string类型。
func GetExcelConfigMapStrStr(data [][]string, startRow, startCol int) (configMap map[string]string, err error) {
	if len(data) < startRow {
		return nil, errors.New("没有发现基本配置信息。")
	}
	if len(data[2]) < startCol+1 {
		return nil, errors.New("没有发现基本配置信息。")
	}
	configMap = make(map[string]string)
	for i := startRow - 1; i < len(data); i++ {
		rowLen := len(data[i])
		if rowLen < startCol {
			break
		}
		k := data[i][startCol-1]
		if k == "" {
			continue
		}
		v := ""
		if rowLen > startCol {
			v = data[i][startCol]
		}
		configMap[k] = v

	}
	return
}

// ExcelInitColCode 初始化列头代码数组, 根据表宽度，生成Excel顶部A-ZZ字符数组,没有考虑ZZ列之后
func ExcelInitColCode(iCol int) (sheetWords []string) {
	if iCol > 26*27 {
		fmt.Println("最大支持到ZZ列")
		return nil
	}

	letter1 := ""
	letter2 := ""

	iLetter1 := 0
	iLetter2 := 0

	for i := 0; i < iCol; i++ {
		iLetter1 = i / 26
		iLetter2 = i % 26
		if iLetter1 == 0 {
			letter1 = ""
		} else {
			letter1 = string(rune(iLetter1 + 64))
		}

		letter2 = string(rune(iLetter2 + 65))

		sheetWords = append(sheetWords, letter1+letter2)
	}

	return
}

// GetSheetWords getSheetWords 根据表宽度，生成Excel顶部A-ZZ字符数组,没有考虑ZZ列之后
func GetSheetWords(iCols int) []string {
	var ret []string
	for i := 0; i < iCols; i++ {
		j := i / 26
		wordL1 := ""
		if j > 0 {
			wordL1 = gotlib.Chr(64 + j%26)
		}
		word := gotlib.Chr(65 + i%26)
		ret = append(ret, wordL1+word)
	}
	return ret
}

// CreateExcelFile fileName 文件名称, sheetName, headerNameArray 表头数组, data为要写入的数据,
func CreateExcelFile(fileName string, sheetName string, headerNameArray []string, data [][]string) (err error) {
	f := excelize.NewFile()
	sheetWords := GetSheetWords(len(headerNameArray))

	for k, v := range headerNameArray {
		f.SetCellValue(sheetName, sheetWords[k]+"1", v)
	}

	//设置列的宽度
	f.SetColWidth(sheetName, "A", sheetWords[len(headerNameArray)-1], 18)
	headStyleID, _ := f.NewStyle(`{
   "font":{
      "color":"#333333",
      "bold":false,
      "family":"arial"
   },
   "alignment":{
      "vertical":"center",
      "horizontal":"center"
   }
}`)
	//设置表头的款式
	f.SetCellStyle(sheetName, "A1", sheetWords[len(headerNameArray)-1]+"1", headStyleID)
	line := 1
	// 循环写入数据
	for _, v := range data {
		line++
		for kk, _ := range headerNameArray {
			f.SetCellValue(sheetName, sheetWords[kk]+strconv.Itoa(line), v[kk])
		}
	}
	// 保存文件
	err = os.Mkdir(gotlib.StrLeftBack(fileName, "/"), 0777)
	if err != nil {
		fmt.Println(err, fileName)
	}
	err = f.SaveAs(fileName)
	if err != nil {
		fmt.Println(err, fileName)
	}

	return err
}
